#import <Foundation/Foundation.h>
#import <objc/runtime.h>
#import <sqlite3.h>
#import <UIKit/UIApplication.h>
#import <LockInfo/Plugin.h>

#import "framework/FWCommon.h"
#import "framework/FWApplication.h"
#import "framework/FWApplicationDelegate.h"
#import "framework/FWPreferences.h"
#import "framework/FWListView.h"
#import "framework/FWDetailListView.h"
#import "framework/FWApplication.h"

#import "PICommon.h"
#import "PIIntegration.h"
#import "PIListAccess.h"
#import "PITodoView.h"
#import "PITodoPreview.h"
#import "PIStatus.h"


static NSString* const kFieldNameContextStatus = @"xxx_contextstat";
static NSString* const kFieldNameChildCount = @"xxx_children";


NSString* const kPIListAccessFieldStartDate = @"start";
NSString* const kPIListAccessFieldDueDate = @"due";
NSString* const kPIListAccessFieldDueTime = @"duetime";
NSString* const kPIListAccessFieldPriority = @"priority";
NSString* const kPIListAccessFieldProject = @"project";
NSString* const kPIListAccessFieldName = @"name";
NSString* const kPIListAccessFieldImportance = @"importance";
NSString* const kPIListAccessFieldFlagged = @"flagged";
NSString* const kPIListAccessFieldIcon = @"icon";
NSString* const kPIListAccessFieldKey = @"key";
NSString* const kPIListAccessFieldAction = @"action";
NSString* const kPIListAccessFieldContext = @"context";
NSString* const kPIListAccessFieldHasDetails = @"hasdetail";
NSString* const kPIListAccessFieldIsRecurring = @"isrecurr";
NSString* const kPIListAccessFieldIsParent = @"isparent";
NSString* const kPIListAccessFieldIsChild = @"ischild";
NSString* const kPIListAccessFieldChildren = @"children";


@implementation PIListAccess


// ******************************************************************************************
#pragma mark -
#pragma mark Bulding SQL search string

-(NSString*) addSortField:(NSString*)current field:(int)field ascending:(BOOL)ascending invert:(BOOL)invert prefs:(FWPreferences*)prefs
{
    NSString* fieldName = nil;
    
    switch (field) {
        case SortFieldOptionsImportance:
            if (invert)
                fieldName = @"case t.priority when 0 then 99 else t.priority end";
            else
                fieldName = @"t.priority";
            fieldName = [fieldName stringByAppendingFormat:@"/*Importance[%i]*/", invert];
            break;
            
        case SortFieldOptionsPriority:
            // Sort by initial letter if present or '-/~' if starts with a number - so numbers precede or follow letters
            // depending upon the invert flag state. If no value at all then use '~~' to ensure no fc value is always
            // at the end of the list.
            fieldName = [NSString stringWithFormat:@"case when ifnull(t.fcpriority,'')='' then '~~' when abs(substr(t.fcpriority,1,1))>0 then '%@' else substr(t.fcpriority,1,1) end", invert ? @"~" : @"-"];
            if (!ascending)
                fieldName = [fieldName stringByAppendingString:@" DESC"];
            fieldName = [fieldName stringByAppendingString:@", case when abs(substr(t.fcpriority,1,1))=0 then abs(substr(t.fcpriority,2)) else abs(t.fcpriority) end"];
            fieldName = [fieldName stringByAppendingFormat:@"/*Priority[%i]*/", invert];
            break;
            
        case SortFieldOptionsAction:
            if (invert)
                fieldName = @"case t.action when 0 then 99 else t.action end";
            else
                fieldName = @"t.action";
            fieldName = [fieldName stringByAppendingFormat:@"/*Action[%i]*/", invert];
            break;
            
        case SortFieldOptionsDueDate:
            // PI stores end times as local rather than UTC so we need to convert the local time to UTC
            // in order to compare with an end date. This will be a hack as srictly we need the number of
            // seconds offset on the given day to account for DST. That's the way that the display functions
            // work.
            if (invert)
                fieldName = [NSString stringWithFormat:@"coalesce(t.duedate,t.duedatetime+ %i,9999999999)",
                             [[NSTimeZone systemTimeZone] secondsFromGMTForDate:[NSDate date]]];
            else
                fieldName = [NSString stringWithFormat:@"ifnull(t.duedate,t.duedatetime+ %i)",
                             [[NSTimeZone systemTimeZone] secondsFromGMTForDate:[NSDate date]]];
            fieldName = [fieldName stringByAppendingFormat:@"/*Due[%i]*/", invert];
            break;
            
        case SortFieldOptionsStartDate:
            if (invert)
                fieldName = @"ifnull(t.startdate,9999999999)";
            else
                fieldName = @"t.startdate";
            fieldName = [fieldName stringByAppendingFormat:@"/*Start[%i]*/", invert];
            break;
            
        case SortFieldOptionsTitle:
            fieldName = @"t.title";
            fieldName = [fieldName stringByAppendingFormat:@"/*Title[%i]*/", invert];
            break;
            
        case SortFieldOptionsReorder:
            fieldName = @"t.sortfield";
            fieldName = [fieldName stringByAppendingFormat:@"/*Reorder[%i]*/", invert];
            break;
            
        case SortFieldOptionsContext:
            fieldName = kFieldNameContextStatus;
            fieldName = [fieldName stringByAppendingFormat:@"/*Context[%i]*/", invert];
            break;
            
        case SortFieldOptionsContextFilter: {
            // This sort is a little tricky. The idea is that if a set of context states may
            // be included in the task list then it should be added to the beginning of the
            // sort list. This prevents, say, high priority out of context items blocking low
            // priority in-context items.
            
            BOOL atStart = [prefs boolValue:kPIOptionsShowContextAtStart withDefault:kPIOptionsShowContextAtStartDefault];
            
            // In-context items should always go at the top of the list
            fieldName = [NSString stringWithFormat:@"case when %@=%i then 0", kFieldNameContextStatus, ContextClassificationInContext];

            // No context items should be at the top of the list if they aren't always filtered
            fieldName = [fieldName stringByAppendingFormat:@" when %@=%i then %i", kFieldNameContextStatus, ContextClassificationNoContext,
                         ([prefs intValue:kPIOptionsHideNoContext withDefault:kPIOptionsHideNoContextDefault] == FilterContextOptionsHideAlways) ? 2 : (atStart ? 1 : 0)];
            
            // Out of context items should be at the top of the list if they aren't always filtered
            fieldName = [fieldName stringByAppendingFormat:@" when %@=%i then %i", kFieldNameContextStatus, ContextClassificationOutOfContext,
                         ([prefs intValue:kPIOptionsHideOutOfContext withDefault:kPIOptionsHideOutOfContextDefault] == FilterContextOptionsHideAlways) ? 2 : (atStart ? 1 : 0)];
            
            fieldName = [fieldName stringByAppendingFormat:@" else 2 end/*ContextFilter[%i]*/", invert];
            break;
        }
            
        default:
            break;
    }
    
    if (fieldName != nil) {
        if ([current length] > 0)
            current = [current stringByAppendingString:@", "];
        current = [current stringByAppendingString:fieldName];
        
        if (!ascending)
            current = [current stringByAppendingString:@" DESC"];
    }
    
    return current;
}

-(NSString*) buildSQLString:(FWApplication*)app prefs:(FWPreferences*)prefs
{
    //NSLog(@"LI:PITodo: Building SQL for prefs %@", prefs);
    
    NSArray* contexts = (NSArray*)[app getAttribute:kPIApplicationContextCurrent];
    NSMutableString* contextList = [NSMutableString stringWithCapacity:100];
    [contextList appendString:@"-1"];
    if (contexts) {
        for (NSDictionary* context in contexts) {
            [contextList appendFormat:@",%i", [[context objectForKey:PIContextFieldNameKey] intValue]];
        }
    }

    BOOL calcParents = [prefs intValue:kPIOptionsShowParent withDefault:kPIOptionsShowParentDefault] == HideParentIfChildren;

    // Get the standard information we'll need
    NSString *allSql = [NSString stringWithFormat:@"select t.title, t.startdate, t.duedate, t.duedatetime, t.priority, t.fcpriority, t.id, t.flagged, t.action, t.icon, t.recurrenceid, p.title, t.isparent, t.parenttaskid, "
                        " case when t.contextid in (%@) then %i when t.contextid=0 then %i else %i end %@,"
                        " %@ %@"
                        " from task t"
                        " left join context c on t.contextid = c.id"
                        " left join task p on t.projectid = p.id"
                        " left join itemtagmap m on m.itemid = t.id"
                        " left join tag g on m.tagid = g.id"
                        " where t.tasktype <> 1 and t.completeddate is null and (t.calendarid=-2 or t.calendarid>=0)",
                        contextList,
                        ContextClassificationInContext,
                        ContextClassificationNoContext,
                        ContextClassificationOutOfContext,
                        kFieldNameContextStatus,
                        calcParents ? @"(select count(*) from task where parenttaskid = t.id)" : @"-1",
                        kFieldNameChildCount];

    // Standard expessions
    NSString* inbox = @"t.contextid<=0 and t.projectid<=0 and t.action<=0 and g.title is null";

    
    
    //allSql = [allSql stringByAppendingString:@" and t.title not like '!!%'/*DEBUG*/"];

    
    // Only generate filter terms if the option to show all tasks is off    
    if (![prefs boolValue:kPIOptionsShowAllTasks withDefault:kPIOptionsShowAllTasksDefault]) {
        int taskFiling = [prefs intValue:kPIOptionsShowFiling withDefault:kPIOptionsShowFilingDefault];
        if (taskFiling == TaskFilingInboxOnly)
            // Only tasks without tags or a context or a project or an action
            allSql = [allSql stringByAppendingFormat:@" and %@/*ShowInboxOnly*/", inbox];
        else if (taskFiling == TaskFilingFiledOnly)
            // Don't allow tasks without tags or a context or a project or an action
            allSql = [allSql stringByAppendingFormat:@" and not(%@)/*ShowFiledOnly*/", inbox];
        
        if ([prefs boolValue:kPIOptionsHideDeferred withDefault:kPIOptionsHideDeferredDefault])
            // Only allow tasks before Delegated
            allSql = [allSql stringByAppendingFormat:@" and t.action<%d/*HideDeferred*/", PIActionDelegated];
        
        // Get the date maxDays in the future
        NSTimeInterval maxDays = (NSTimeInterval)[prefs intValue:kPIOptionsDayLimit withDefault:kPIOptionsDayLimitDefault];
        NSTimeInterval seconds = maxDays * SECONDS_PER_DAY;
        
        // Get time of midnight today (ie past) in the UTC by building a new date from only the year, month
        // and day components of the current date. PI start dates are always UTC
        NSDate *midnight = [PIIntegration utcDateForLocalMidnight];
        //DebugLog(@"LI:PITodo: Midnight date = %@", midnight);
        
        if ([prefs boolValue:kPIOptionsHideFuture withDefault:kPIOptionsHideFutureDefault])
            // Only allow tasks starting before or at today's midnight or that don't have a start date
            allSql = [allSql stringByAppendingFormat:@" and ifnull(t.startdate,0)<=%i/*HideFuture*/", (int)[midnight timeIntervalSinceReferenceDate]];
        
        // Get future midnight date by adding the number of days we will show (as seconds). Again this will be in UTC
        NSDate* future = [midnight addTimeInterval:seconds];
        //DebugLog(@"LI:PITodo: Cutoff date = %@", future);
        
        // PI stores end times as local rather than UTC so we need to convert the local time to UTC
        // in order to compare with an end date. This will be a hack as srictly we need the number of
        // seconds offset on the given day to account for DST. That's the way that the display functions
        // work.
        NSInteger utcOffset = [[NSTimeZone systemTimeZone] secondsFromGMTForDate:midnight];
        DebugLog(@"LI:PITodo: UTC offset to local time = %i", utcOffset);
        
        // Only allow items with no duedate or a duedate before the threshold (not at as that would give 1 day too much)
        allSql = [allSql stringByAppendingFormat:@" and coalesce(t.duedate,%i+t.duedatetime,0)<%i/*MaxLimit-%i*/", utcOffset, (int)[future timeIntervalSinceReferenceDate], (int)maxDays];
        
        // Removed option as too confusing!
        //if ([prefs boolValue:kPIOptionsLockscreenOnly withDefault:kPIOptionsLockscreenOnlyDefault])
        //    // Only allow items with a tag named 'Lockscreen'
        //    allSql = [allSql stringByAppendingString:@" and g.title like 'Lockscreen'/*TagLockscreen*/"];
        
        if (NSString* tagFilter = [[prefs stringValue:kPIOptionsTagFilter withDefault:kPIOptionsTagFilterDefault] stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceCharacterSet]]) {
            if ([tagFilter length] > 0) {
                // Only allow items with a tag that matches the requested value
                tagFilter = [tagFilter stringByReplacingOccurrencesOfString:@"'" withString:@"''"];
                allSql = [allSql stringByAppendingFormat:@" and g.title like '%@'/*TagAny*/", tagFilter];
            }
        }

        int showParents = [prefs intValue:kPIOptionsShowParent withDefault:kPIOptionsShowParentDefault];
        if (showParents == HideParentIfChildren)
            // Only allow items that don't have children
            allSql = [allSql stringByAppendingFormat:@" and %@<=0/*HideWithChild*/", kFieldNameChildCount];
        
        if ([prefs boolValue:kPIOptionsHideUndated withDefault:kPIOptionsHideUndatedDefault])
            // Only allow items that don't have a duedate or duedatetime
            allSql = [allSql stringByAppendingString:@" and ifnull(t.duedate,t.duedatetime) is not null/*HideUndated*/"];
        
        int actionType = [prefs intValue:kPIOptionsShowOnlyAction withDefault:kPIOptionsShowOnlyActionDefault];
        if (actionType >= 0)
            // Only allow items with the correct action
            allSql = [allSql stringByAppendingFormat:@" and t.action=%i/*OnlyAction*/", actionType];
        
        if ([prefs boolValue:kPIOptionsStarredOnly withDefault:kPIOptionsStarredOnlyDefault])
            // Only allow items that have been flagged
            allSql = [allSql stringByAppendingString:@" and t.flagged=1/*StarredOnly*/"];
    }
    else
        allSql = [allSql stringByAppendingString:@" /*ShowAllTasks*/"];
    
    allSql = [allSql stringByAppendingString:@" group by t.id"];
    
    // Add sorting terms
    NSString* sortExpr = @"";
    
    int sort1 = [prefs intValue:kPIOptionsSortField1 withDefault:kPIOptionsSortField1Default];
    int sort2 = [prefs intValue:kPIOptionsSortField2 withDefault:kPIOptionsSortField2Default];

    if (((PIStatus*)app.info).isAutoContextOn || ((PIStatus*)app.info).isFixedContextOn && (sort1 != SortFieldOptionsContext) && (sort2 != SortFieldOptionsContext))
        sortExpr = [self addSortField:sortExpr field:SortFieldOptionsContextFilter ascending:YES invert:NO prefs:prefs];
    
    BOOL sortAsc1 = [prefs boolValue:kPIOptionsSortField1Asc withDefault:kPIOptionsSortField1AscDefault];
    int noneLast1 = [prefs intValue:kPIOptionsSortField1Order withDefault:kPIOptionsSortField1OrderDefault];
    sortExpr = [self addSortField:sortExpr field:sort1 ascending:sortAsc1 invert:(noneLast1 == SortFieldNoneOrderLast) prefs:prefs];
    
    BOOL sortAsc2 = [prefs boolValue:kPIOptionsSortField2Asc withDefault:kPIOptionsSortField2AscDefault];
    int noneLast2 = [prefs intValue:kPIOptionsSortField2Order withDefault:kPIOptionsSortField2OrderDefault];
    if (sort1 != sort2)
        sortExpr = [self addSortField:sortExpr field:sort2 ascending:sortAsc2 invert:(noneLast2 == SortFieldNoneOrderLast) prefs:prefs];
    
    if ([sortExpr length] > 0)
        allSql = [allSql stringByAppendingFormat:@" order by %@", sortExpr];
    
    // Restrict the number of items returned to the requested number		
    int queryLimit = [prefs intValue:kPIOptionsMaxTasks withDefault:kPIOptionsMaxTasksDefault];
    allSql = [allSql stringByAppendingFormat:@" limit %i", queryLimit];
    
    return allSql;
}

- (NSString*)sqlStringForChildCount:(NSArray*)items
{
    NSMutableString* keys = [NSMutableString stringWithCapacity:50];

    for (NSDictionary* item in items) {
        NSNumber* isChild = [item objectForKey:kPIListAccessFieldIsChild];
        if ([isChild intValue] == 0) {
            NSNumber* isParent = [item objectForKey:kPIListAccessFieldIsParent];
            if ([isParent intValue] == 0) {
                NSNumber* key = [item objectForKey:kPIListAccessFieldKey];
                if ([keys length] > 0)
                    [keys appendString:@","];
                [keys appendFormat:@"%i", [key intValue]];
            }
        }
    }
    
    if ([keys length] == 0)
        return @"";
    else
        return [NSString stringWithFormat:@"select parenttaskid, count(*) from task where parenttaskid in (%@) group by parenttaskid", keys];
}

// ******************************************************************************************
#pragma mark -
#pragma mark Fetching data from the database

- (NSArray*)application:(FWApplication*)application fetchNewDataForPrefs:(FWPreferences*)prefs
{
    NSBundle* bundle = [NSBundle bundleForClass:[self class]];
    PIStatus* status = (PIStatus*)application.info;
    
    // Read current display settings
    status.shortDates = [prefs intValue:kPIOptionsShortDates withDefault:kPIOptionsShortDatesDefault];
    status.showIcons = [prefs boolValue:kPIOptionsShowIcons withDefault:kPIOptionsShowIconsDefault];
    status.showContexts = [prefs intValue:kPIOptionsShowContexts withDefault:kPIOptionsShowContextsDefault];
    status.singleLineOniPad = [prefs boolValue:kPIOptionsiPadSingleLine withDefault:kPIOptionsiPadSingleLineDefault];
    status.showEmoji = [prefs boolValue:kPIOptionsShowEmoji withDefault:kPIOptionsShowEmojiDefault]; 
    
    // Save the current setting of the show details flag
    NSInteger showDetails = [prefs intValue:kPIOptionsShowDates withDefault:kPIOptionsShowDatesDefault];
    if (showDetails != status.showListDetails) {
        DebugLog(@"LI:PITodo: Details display changed - reset toggle mode");
        status.toggleOn = NO;
        status.showListDetails = showDetails;
    }
    
        // If using automatic contexts then get the latest values
    if (status.isAutoContextOn) {
        // Update the context update count if there is one
        NSInteger changeCount = -1;
        if (UIPasteboard* pasteBoard = [UIPasteboard pasteboardWithName:PIIntegrationContextPasteboard create:NO])
            changeCount = [pasteBoard changeCount];

        // If change count has changed then record the new value and get the latest contexts
        if (changeCount != status.autoContextPasteboardUpdateCount) {
            status.autoContextPasteboardUpdateCount = changeCount;
            [application setAttribute:kPIApplicationContextCurrent value:[PIIntegration locationContextsFromPI]];
        }
    }
    
    // Get the current data
    NSString* sql = [self buildSQLString:application prefs:prefs];
    NSArray* newTodos = FWDBGetRecords(sql, 1, application.databasePath, application, self);
    if (!newTodos)
        newTodos = [NSArray array];
    
    NSArray* contextList = [application getAttribute:kPIApplicationContextCurrent];
    if (contextList) {
        NSMutableString* contextNames = [NSMutableString stringWithCapacity:80];
        for (NSDictionary* context in contextList) {
            if ([contextNames length] > 0)
                [contextNames appendString:@", "];
            [contextNames appendString:[context objectForKey:PIContextFieldNameTitle]];
        }
        
        if ([contextNames length] > 0)
            ((PIStatus*)application.info).captionValue = contextNames;
        else
            ((PIStatus*)application.info).captionValue = localize(bundle, @"Context/Label/None");
    }
    
    // Examine the data set
    BOOL contextFound = NO;
    NSInteger returnedCount = [newTodos count];
    NSInteger filteredCount = 0;
    NSInteger detailsCount = 0;
    
    // Check if any of the selected contexts were found in the dataset
    for (NSDictionary* element in newTodos) {
        if ([[element objectForKey:kPIListAccessFieldContext] intValue] == ContextClassificationInContext)
            contextFound = YES;
        
        if ([[element objectForKey:kPIListAccessFieldHasDetails] boolValue])
            ++detailsCount;
    }
    
    if (contextFound) {
        NSInteger hideNoContext = [prefs intValue:kPIOptionsHideNoContext withDefault:kPIOptionsHideNoContextDefault];
        NSInteger hideOutOfContext = [prefs intValue:kPIOptionsHideOutOfContext withDefault:kPIOptionsHideOutOfContextDefault];
        
        NSMutableArray* filteredList = [NSMutableArray arrayWithCapacity:[newTodos count]];
        NSMutableArray* lastList = [NSMutableArray arrayWithCapacity:[newTodos count]];
        BOOL contextAtStart = [prefs boolValue:kPIOptionsShowContextAtStart withDefault:kPIOptionsShowContextAtStartDefault];

        for (NSDictionary* element in newTodos) {
#ifdef DEBUG
            NSString* title = [element objectForKey:kPIListAccessFieldName];
#endif
            NSInteger context = [[element objectForKey:kPIListAccessFieldContext] intValue];
            BOOL filter = NO;
            
            if (context == ContextClassificationInContext) {
                // Do nothing
            }
            else if (context == ContextClassificationOutOfContext) {
                if (hideOutOfContext == FilterContextOptionsHideAlways) {
                    DebugLog(@"LI:PITodo: Filtering out of context always - %@", title);
                    filter = YES;
                }
                else if (hideOutOfContext == FilterContextOptionsHideIfMatch) {
                    DebugLog(@"LI:PITodo: Filtering out of context since match found - %@", title);
                    filter = contextFound;
                }
            }
            else if (context == ContextClassificationNoContext) {
                if (hideNoContext == FilterContextOptionsHideAlways) {
                    DebugLog(@"LI:PITodo: Filtering no context always - %@", title);
                    filter = YES;
                }
                else if (hideNoContext == FilterContextOptionsHideIfMatch) {
                    DebugLog(@"LI:PITodo: Filtering no context since match found - %@", title);
                    filter = contextFound;
                }
            }
            
            if (filter) {
                // Update totals to incidate am item is now missing
                if ([[element objectForKey:kPIListAccessFieldHasDetails] boolValue])
                    --detailsCount;
                ++filteredCount;
            }
            else {
                if ((context == ContextClassificationInContext) || !contextAtStart)
                    [filteredList addObject:element];
                else
                    [lastList addObject:element];
            }
        }

        // If we have any items we've moved down the list then add them back to the end
        if ([lastList count] > 0)
            [filteredList addObjectsFromArray:lastList];
        
        // Use the filtered list
        newTodos = filteredList;
    }
    
    status.itemsReturnedCount = returnedCount;
    status.itemsWithDetailsCount = detailsCount;
    status.itemsFilteredCount = filteredCount;

    // If we have already calculated the child counts in order to filter parents then we don't need to do it again
    BOOL calcChildren = [prefs intValue:kPIOptionsShowParent withDefault:kPIOptionsShowParentDefault] == HideParentIfChildren;
    if (!calcChildren && [prefs boolValue:kPIOptionsShowParentIcons withDefault:kPIOptionsShowParentIconsDefault]) {
        // Get a list of items to calculate the child counts for
        NSString* childCountsSql = [self sqlStringForChildCount:newTodos];
        
        if ([childCountsSql length] > 0) {
            NSArray* childCounts = FWDBGetRecords(childCountsSql, 2, application.databasePath, application, self);
            NSLog(@"LI:PITodo: Manually applying %i child counts", [childCounts count]);

            for (NSDictionary* childCount in childCounts) {
                NSNumber* children = [childCount objectForKey:kPIListAccessFieldChildren];
                if ([children intValue] > 0) {
                    NSNumber* parent = [childCount objectForKey:kPIListAccessFieldKey];
                    for (NSMutableDictionary* todo in newTodos) {
                        NSNumber* key = [todo objectForKey:kPIListAccessFieldKey];
                        if ([key intValue] == [parent intValue]) {
                            [todo setValue:[NSNumber numberWithInt:1] forKey:kPIListAccessFieldIsParent];
                            [todo setValue:children forKey:kPIListAccessFieldChildren];
                            break;
                        }
                    }
                }
            }
        }
        else
            NSLog(@"LI:PITodo: No child counts required");
    }
    else
        NSLog(@"LI:PITodo: Child counts by SQL");
    
    // Use the original data list
    return newTodos;
}

- (NSObject*)objectForSQLRecord:(sqlite3_stmt*)sqlStatement withId:(NSInteger)sqlid application:(FWApplication*)application
{
    if (sqlid == 1) {
        // Read fields from SQL record
        const char *cText = (const char*)sqlite3_column_text(sqlStatement, 0);
        double cStart = sqlite3_column_double(sqlStatement, 1);
        double cDue = sqlite3_column_double(sqlStatement, 2);
        double cDueTime = sqlite3_column_double(sqlStatement, 3);
        int importance = sqlite3_column_int(sqlStatement, 4);
        const char *cPriority = (const char*)sqlite3_column_text(sqlStatement, 5);
        int primaryKey = sqlite3_column_int(sqlStatement, 6);
        int flagged = sqlite3_column_int(sqlStatement, 7);
        int action = sqlite3_column_int(sqlStatement, 8);
        const char *cIcon = (const char*)sqlite3_column_text(sqlStatement, 9);
        int recurrance = sqlite3_column_int(sqlStatement, 10);
        const char *cProject = (const char*)sqlite3_column_text(sqlStatement, 11);
        int parent = sqlite3_column_int(sqlStatement, 12);
        int child = sqlite3_column_int(sqlStatement, 13);
        int context = sqlite3_column_int(sqlStatement, 14);
        int children = sqlite3_column_int(sqlStatement, 15);
        
        NSString *aText = [NSString stringWithUTF8String:(cText == NULL ? "" : cText)];
        NSString *aIcon = [NSString stringWithUTF8String:(cIcon == NULL ? "" : cIcon)];
        NSString *aPriority = [NSString stringWithUTF8String:(cPriority == NULL ? "" : cPriority)];
        NSString *aProject = [NSString stringWithUTF8String:(cProject == NULL ? "" : cProject)];
        
        BOOL detailsPresent = ((cStart != 0) ||
                               (cDue != 0) ||
                               (cDueTime != 0) ||
                               ([aPriority length] > 0) ||
                               ([aProject length] > 0));
        
        // Create a dictionary containing the fields we want to display
        NSDictionary *todoDict = [NSMutableDictionary dictionaryWithObjectsAndKeys:
                                  aText, kPIListAccessFieldName,
                                  [NSNumber numberWithDouble:cStart], kPIListAccessFieldStartDate,
                                  [NSNumber numberWithDouble:cDue], kPIListAccessFieldDueDate,
                                  [NSNumber numberWithDouble:cDueTime], kPIListAccessFieldDueTime,
                                  [NSNumber numberWithInt:importance], kPIListAccessFieldImportance, 
                                  aPriority, kPIListAccessFieldPriority,
                                  [NSNumber numberWithInt:flagged], kPIListAccessFieldFlagged, 
                                  aIcon, kPIListAccessFieldIcon,
                                  aProject, kPIListAccessFieldProject,
                                  [NSNumber numberWithInt:primaryKey], kPIListAccessFieldKey, 
                                  [NSNumber numberWithInt:action], kPIListAccessFieldAction,
                                  [NSNumber numberWithBool:detailsPresent], kPIListAccessFieldHasDetails,
                                  [NSNumber numberWithInt:recurrance], kPIListAccessFieldIsRecurring,
                                  [NSNumber numberWithInt:context], kPIListAccessFieldContext,
                                  [NSNumber numberWithInt:parent | (children > 0)], kPIListAccessFieldIsParent,
                                  [NSNumber numberWithInt:child], kPIListAccessFieldIsChild,
                                  [NSNumber numberWithInt:children], kPIListAccessFieldChildren,
                                  nil];
        
        return todoDict;
    }
    else {
        int primaryKey = sqlite3_column_int(sqlStatement, 0);
        int children = sqlite3_column_int(sqlStatement, 1);
        
        NSDictionary *todoDict = [NSDictionary dictionaryWithObjectsAndKeys:
                                  [NSNumber numberWithInt:primaryKey], kPIListAccessFieldKey, 
                                  [NSNumber numberWithInt:children], kPIListAccessFieldChildren,
                                  nil];
        
        return todoDict;
    }
}

- (BOOL)application:(FWApplication*)application shouldUpdateForDate:(NSDate*)date
{
    if (((PIStatus*)application.info).isAutoContextOn) {
        if (UIPasteboard* pasteBoard = [UIPasteboard pasteboardWithName:PIIntegrationContextPasteboard create:NO]) {
            NSInteger lastUpdates = ((PIStatus*)application.info).autoContextPasteboardUpdateCount;
            NSInteger newUpdates = [pasteBoard changeCount];
            if (newUpdates > lastUpdates) {
                NSLog(@"LI:PITodo: Context list changed - old=%i, new=%i - update required", lastUpdates, newUpdates);
                return YES;
            }
        }
    }
    
    return NO;
}    


// ******************************************************************************************
#pragma mark -
#pragma mark Describing the layout

- (NSInteger)tableView:(LITableView*)tableView totalNumberOfItems:(FWApplication*)application preferences:(FWPreferences*)prefs
{
    PIStatus* info = (PIStatus*)application.info;
    return info.itemsReturnedCount;
}

- (BOOL)shouldShowDetailLine:(NSDictionary*)elem application:(FWApplication*)app preferences:(FWPreferences*)prefs forItem:(BOOL)forItem
{
    int showDate = ((PIStatus*)app.info).showListDetails;
    BOOL toggleActive = ((PIStatus*)app.info).isToggleOn;
    
    if (showDate == ShowDatesAlways) {
        // If always showing dates then show unless the toggle is present
        return !toggleActive;
    }
    else if (showDate == ShowDatesNever) {
        // If never showing dates then don't show unless the toggle is present
        return toggleActive;
    }
    else if (toggleActive) {
        // Field is optional. If toggle is active then if no details were present for any
        // item then withoug the toggle, nothing was shown. So, show all details as the toggle
        // state. Otherwise show no details as the toggle state as some items must have
        // been shown without the toggle.
        return ((PIStatus*)app.info).itemsWithDetailsCount == 0;
    }
    
    if (forItem)
        return [[elem objectForKey:kPIListAccessFieldHasDetails] boolValue];
    else
        return NO;
}

- (CGFloat)tableView:(LITableView *)tableView heightForItems:(NSDictionary*)elem application:(FWApplication*)application preferences:(FWPreferences*)prefs
{
    BOOL showDetail = [self shouldShowDetailLine:elem application:application preferences:prefs forItem:YES];
    BOOL singleLine = ((PIStatus*)application.info).isSingleLineOniPad;
    
    if (FWIsDeviceAnIPad() && singleLine) {
        CGFloat summaryHeight = [PITodoView heightOfSummaryRowWithFont:tableView.theme.summaryStyle.font withFollowingRow:NO];
        return summaryHeight;
    }
    else {
        CGFloat summaryHeight = [PITodoView heightOfSummaryRowWithFont:tableView.theme.summaryStyle.font withFollowingRow:showDetail];
        if (showDetail) {
            CGFloat detailHeight = [PITodoView heightOfDetailRowWithFont:tableView.theme.detailStyle.font];
            return summaryHeight + detailHeight;
        }
        else
            return summaryHeight;
    }
}


// ******************************************************************************************
#pragma mark -
#pragma mark Providing data for the display

- (FWListView*)application:(FWApplication*)app cellViewForTableView:(LITableView*)tableView  preferences:(FWPreferences*)prefs
{
    // We can hard-code the height at this time as this will vary dynamically depending upon content
    return [[PITodoView alloc] initWithFrame:CGRectMake(0, 0, tableView.bounds.size.width, 35) table:tableView application:app];
}

- (void)tableView:(LITableView*)tableView setDetailsUsingView:(FWListView*)view forItem:(NSDictionary*)dict application:(FWApplication*)app preferences:(FWPreferences*)prefs
{
    NSBundle* bundle = [NSBundle bundleForClass:[self class]];
    PIStatus* status = (PIStatus*)app.info;
    
    NSString* name = [dict objectForKey:kPIListAccessFieldName];
    NSString* priority = [dict objectForKey:kPIListAccessFieldPriority];
    NSString* project = [dict objectForKey:kPIListAccessFieldProject];
    NSString* icon = [dict objectForKey:kPIListAccessFieldIcon];
    
    if (status.isShowEmoji && ([icon length] > 0))
        name = [NSString stringWithFormat:@"%@ %@", name, icon];
    
    int importance = [[dict objectForKey:kPIListAccessFieldImportance] intValue];
    int flagged = [[dict objectForKey:kPIListAccessFieldFlagged] intValue];
    int action = [[dict objectForKey:kPIListAccessFieldAction] intValue];
    int context = [[dict objectForKey:kPIListAccessFieldContext] intValue];
    int parent = [[dict objectForKey:kPIListAccessFieldIsParent] intValue];
    int child = [[dict objectForKey:kPIListAccessFieldIsChild] intValue];
    int repeat = [[dict objectForKey:kPIListAccessFieldIsRecurring] intValue];
    
    NSString* due = nil;
    NSString* shortDue = nil;
    BOOL overdue = false;
    
    BOOL dateLine = [self shouldShowDetailLine:dict application:app preferences:prefs forItem:YES];
    
    if (dateLine) {		
        BOOL showOverdue = [prefs boolValue:kPIOptionsHighlightOverdue withDefault:kPIOptionsHighlightOverdueDefault];
        NSNumber* startNum = [dict objectForKey:kPIListAccessFieldStartDate];
        NSNumber* dateNum = [dict objectForKey:kPIListAccessFieldDueDate];
        NSNumber* timeNum = [dict objectForKey:kPIListAccessFieldDueTime];
        
        // If there is a due date/time then use that instead of the due date
        if (timeNum.doubleValue != 0)
            dateNum = timeNum;
        if ((dateNum.doubleValue == 0.0) && (startNum.doubleValue == 0.0)) {
            due = localize(bundle, @"Date/No Due Date");
        }
        else {
            BOOL timeElement = (timeNum.doubleValue != 0);
            NSDate* startDate = [PIIntegration localDateForDateNumber:startNum];
            NSDate* dueDate =  timeElement ? [PIIntegration localDateForTimeNumber:timeNum]
            : [PIIntegration localDateForDateNumber:dateNum];
            NSDate *today = [NSDate date];
            
            if (showOverdue && (dateNum.doubleValue != 0) && ([dueDate compare:today] == NSOrderedAscending)) 
                overdue = true;
            
            // If start and due are the same day then only display the one date, otherwise display the range
            if (isSameDay(startDate, dueDate)) {
                due = [PIIntegration dateStringForDateNumber:dateNum
                                                    withTime:timeElement
                                                      format:DateFormatterNormalFormat];
                shortDue = [PIIntegration dateStringForDateNumber:dateNum
                                                         withTime:timeElement
                                                           format:DateFormatterCompressedFormat];
            }
            else {
                due = [NSString stringWithFormat:@"%@ \u2014 %@",
                       [PIIntegration dateStringForDateNumber:startNum
                                                     withTime:false 
                                                       format:DateFormatterNormalFormat],
                       [PIIntegration dateStringForDateNumber:dateNum
                                                     withTime:timeElement
                                                       format:DateFormatterNormalFormat]];
                shortDue = [NSString stringWithFormat:@"%@ \u2014 %@",
                            [PIIntegration dateStringForDateNumber:startNum
                                                          withTime:false 
                                                            format:DateFormatterCompressedFormat],
                            [PIIntegration dateStringForDateNumber:dateNum
                                                          withTime:timeElement
                                                            format:DateFormatterCompressedFormat]];
            }
        }
    }
    
    // If we have a project value then display it in the priority field
    if (([priority length] == 0) && ([project length] > 0))
        priority = project;

    BOOL showChildIcons = [prefs boolValue:kPIOptionsShowParentIcons withDefault:kPIOptionsShowParentIconsDefault];
    [(PITodoView*)view setViewForTable:tableView
                           application:app
                                 title:name
                                   due:status.shortDates == ShortDatesAlways ? shortDue : due
                              dueShort:status.shortDates == ShortDatesNever ? due : shortDue
                               overdue:overdue
                            importance:importance
                              priority:priority
                                action:action
                               flagged:flagged
                                parent:parent && showChildIcons
                                 child:child && showChildIcons
                               context:context == ContextClassificationInContext
                             showIcons:status.isShowIcons
                                repeat:repeat != 0];
    
    // Do we want everything on one line for an iPad?
    if ((view.singleLine = FWIsDeviceAnIPad()) && status.isSingleLineOniPad) {
        if (status.showListDetails != ShowDatesIfNeeded)
            view.expandSingleLine = ![self shouldShowDetailLine:dict application:app preferences:prefs forItem:NO];
        else if (status.isToggleOn)
            view.expandSingleLine = status.itemsWithDetailsCount != 0;
        else
            view.expandSingleLine = status.itemsWithDetailsCount == 0;
    }
}

- (NSString*)tableView:(LITableView*)tableView detailForHeaderInSection:(NSInteger)section application:(FWApplication*)app preferences:(FWPreferences*)prefs
{
    NSBundle* bundle = [NSBundle bundleForClass:[self class]];
    NSInteger showContexts = ((PIStatus*)app.info).showContexts;
    
    if ([prefs boolValue:kPIOptionsShowAllTasks withDefault:kPIOptionsShowAllTasksDefault])
        return localize(bundle, @"Filters/Disabled");
    
    if (showContexts == ShowContextsNever)
        return nil;
    else if (((PIStatus*)app.info).isAutoContextOn) {
        NSArray* contexts = (NSArray*)[app getAttribute:kPIApplicationContextCurrent];
        if (!contexts || ([contexts count] == 0))
            return localize(bundle, @"Context/Auto/0");
        else if ([contexts count] == 1)
            return localize(bundle, @"Context/Auto/1");
        else
            return [NSString stringWithFormat:localize(bundle, @"Context/Auto/X"), [contexts count]]; 
    }
    else if (((PIStatus*)app.info).isFixedContextOn) {
        NSArray* contexts = (NSArray*)[app getAttribute:kPIApplicationContextCurrent];
        if (!contexts || ([contexts count] == 0))
            return localize(bundle, @"Context/Manual/0");
        else if ([contexts count] == 1)
            return localize(bundle, @"Context/Manual/1");
        else
            return [NSString stringWithFormat:localize(bundle, @"Context/Manual/X"), [contexts count]]; 
    }
    else if (showContexts == ShowContextsAlways)
        return localize(bundle, @"Context/Off");
    else 
        return nil;
}


// ******************************************************************************************
#pragma mark -
#pragma mark Managing the toolbar

- (BOOL)application:(FWApplication *)app isToolbarRequiredForPrefs:(FWPreferences *)prefs
{
    return [prefs boolValue:kPIOptionsShowToolbar withDefault:kPIOptionsShowToolbarDefault];
}

- (NSString*)application:(FWApplication*)app toolbarCaptionForPrefs:(FWPreferences*)prefs
{
    return ((PIStatus*)app.info).captionValue;
}

- (NSString*)application:(FWApplication*)app toolbarBadgeForPrefs:(FWPreferences*)prefs
{
    if ([prefs boolValue:kPIOptionsShowBadge withDefault:kPIOptionsShowBadgeDefault])
        return ((PIStatus*)app.info).badgeValue;
    else
        return nil;
}

static NSInteger const kLocationBtnTag = 1;
static NSInteger const kFavoriteBtnTag = 2;
static NSInteger const kDetailsBtnTag = 3;
static NSInteger const kDisableBtnTag = 4;
static NSInteger const kNewTodoBtnTag = 5;

- (NSArray*)application:(FWApplication*)app toolbarItemsForPrefs:(FWPreferences*)prefs
{
    NSBundle* bundle = [NSBundle bundleForClass:[self class]];

    UIBarButtonItem* locationBtn = [[[UIBarButtonItem alloc]
                                     initWithImage:[UIImage imageWithContentsOfFile:[bundle pathForResource:@"locate" ofType:@"png"]]
                                     style:UIBarButtonItemStylePlain
                                     target:app.plugin
                                     action:@selector(locationContextClicked)] autorelease];
    locationBtn.tag = kLocationBtnTag;

    UIBarButtonItem* newTodoBtn = [[[UIBarButtonItem alloc]
                                    initWithImage:[UIImage imageWithContentsOfFile:[bundle pathForResource:@"newtodo" ofType:@"png"]]
                                    style:UIBarButtonItemStylePlain
                                    target:app.plugin
                                    action:@selector(newTodoClicked)] autorelease];
    newTodoBtn.tag = kNewTodoBtnTag;
    
    UIBarButtonItem* favoriteBtn = [[[UIBarButtonItem alloc]
                                     initWithImage:[UIImage imageWithContentsOfFile:[bundle pathForResource:@"favorite" ofType:@"png"]]
                                     style:UIBarButtonItemStylePlain
                                     target:app.plugin
                                     action:@selector(manualContextClicked)] autorelease];
    favoriteBtn.tag = kFavoriteBtnTag;
    
    UIBarButtonItem* detailsBtn = [[[UIBarButtonItem alloc]
                                    initWithImage:[UIImage imageWithContentsOfFile:[bundle pathForResource:@"magnify" ofType:@"png"]]
                                    style:UIBarButtonItemStylePlain
                                    target:app.plugin
                                    action:@selector(toggleDetailsClicked)] autorelease];
    detailsBtn.tag = kDetailsBtnTag;
    
    UIBarButtonItem* disableBtn = [[[UIBarButtonItem alloc]
                                    initWithImage:[UIImage imageWithContentsOfFile:[bundle pathForResource:@"cancel" ofType:@"png"]]
                                    style:UIBarButtonItemStylePlain
                                    target:app.plugin
                                    action:@selector(disableContextsClicked)] autorelease];
    disableBtn.tag = kDisableBtnTag;
    
    UIBarButtonItem *flexSpc = [[UIBarButtonItem alloc]
                                initWithBarButtonSystemItem:UIBarButtonSystemItemFlexibleSpace
                                target:nil
                                action:nil];
    
    UIBarButtonItem *separator = [[UIBarButtonItem alloc]
                                  initWithBarButtonSystemItem:UIBarButtonSystemItemFixedSpace
                                  target:nil
                                  action:nil];
    separator.width = 5;
    
    NSMutableArray* navItems = [NSMutableArray arrayWithObjects:
                                locationBtn,
                                favoriteBtn,
                                disableBtn,
                                separator,
                                detailsBtn,
                                nil];
    
    if (app.isLockInfo3 && [prefs boolValue:kPIOptionsAddTaskFeature withDefault:kPIOptionsAddTaskFeatureDefault])
        [navItems addObject:newTodoBtn];

    [navItems addObject:flexSpc];
    
    return navItems;
}

- (BOOL)application:(FWApplication *)app shouldRebuildToolbar:(NSArray *)items prefs:(FWPreferences *)prefs
{
    BOOL todoPresent = NO;
    
    for (UIBarButtonItem* item in items) {
        if (item.tag == kNewTodoBtnTag) {
            todoPresent = YES;
            break;
        }
    }
    
    if ([prefs boolValue:kPIOptionsAddTaskFeature withDefault:kPIOptionsAddTaskFeatureDefault])
        return !todoPresent;
    else
        return todoPresent;
}

- (BOOL)application:(FWApplication*)app configureToolbarItems:(NSArray*)items prefs:(FWPreferences*)prefs
{
    PIStatus* status = (PIStatus*)app.info;
    for (UIBarButtonItem* item in items) {
        if (item.tag == kLocationBtnTag)
#ifdef DEVELOPMENT_MODE
            item.enabled = !status.isAutoContextOn;
#else
            item.enabled = status.isAutoContextAvailable && !status.isAutoContextOn;
#endif
        else if (item.tag == kFavoriteBtnTag)
            item.enabled = YES;
        else if (item.tag == kDisableBtnTag)
            item.enabled = status.isFixedContextOn || status.isAutoContextOn;
        else if (item.tag == kNewTodoBtnTag)
            item.enabled = YES;
    }
    
    return YES;
}

- (NSInteger)application:(FWApplication *)app toolbarIndentForPrefs:(FWPreferences *)prefs
{
    if ([prefs boolValue:kPIOptionsAddTaskFeature withDefault:kPIOptionsAddTaskFeatureDefault])
        return 127;
    else
        return 108;
}


// ******************************************************************************************
#pragma mark -
#pragma mark Implement preview display

- (UIView*)tableView:(LITableView*)tableView previewWithFrame:(CGRect)rect atIndex:(NSInteger)index forItems:(NSArray*)list application:(FWApplication*)app preferences:(FWPreferences*)prefs
{   
    LIPlugin* plugin = (LIPlugin*)[app.plugin plugin];
    NSNumber* show = [plugin.preferences objectForKey:kPIOptionsShowPreviews];
    if (!show)
        show = [NSNumber numberWithBool:kPIOptionsShowPreviewsDefault];
    
    if (![show boolValue])
        return nil;
    
    PITodoPreview* previewData = [[[PITodoPreview alloc] init] autorelease];
    FWDetailListView *thePreview = [[[FWDetailListView alloc] initWithFrame:rect
                                                                       list:list
                                                                      index:index
                                                                    caption:@"ListView/Item %i/%i"
                                                                application:app
                                                                   delegate:previewData] autorelease];
    
    thePreview.backgroundColor = [UIColor whiteColor];
    
    return thePreview;
}

@end
